
--select * from dbo.ufn_Org_GetDeptTree('FF11CA49-E3F3-4400-98FB-5D9E4576CE71')
CREATE FUNCTION [dbo].[ufn_Org_GetDeptTree](@BusinessUnitId UNIQUEIDENTIFIER)
RETURNS @data TABLE(BusinessUnitId UNIQUEIDENTIFIER)
AS
BEGIN
	WITH DataTree(BusinessUnitId ,ParentBusinessUnitId) AS
	(
		SELECT BusinessUnitId,ParentBusinessUnitId FROM BusinessUnit WHERE BusinessUnitId = @BusinessUnitId
		UNION ALL
		SELECT I.BusinessUnitId,I.ParentBusinessUnitId 
		FROM BusinessUnit AS I INNER JOIN DataTree AS DT ON I.ParentBusinessUnitId = DT.BusinessUnitId AND I.BusinessUnitId <> DT.BusinessUnitId
	)
	INSERT INTO @data
	SELECT BusinessUnitId FROM DataTree
	RETURN
END
GO


